--创建库
create DATABASE printer

--创建设备表
--判断表是否存在，存在先删除
-- use printer
use printer
if exists( select * from sys.sysobjects where name='print_device')
  drop table print_device
go

CREATE TABLE print_device(
	id int identity(1,1) not null PRIMARY KEY,
	device_number varchar(50),--设备编号
	ip varchar(50),--ip地址
	port int,--端口
	is_connection int DEFAULT 1,--是否连接打印机: 0,不连接；1,连接。
	creatime datetime  DEFAULT GETDATE()
)
go
-- 
-- insert into print_device 
-- values('p1','127.0.0.1',9999,1,GETDATE()),('p2','127.0.0.1',8888,1,GETDATE())
-- go
-- 
-- DECLARE @count int = 1;
-- DECLARE @devno varchar(50)
-- DECLARE @ip varchar(50)
-- while @count<=20
-- 
-- begin
-- set @devno = cast(@count as varchar(50))
-- set @ip = '10.66.252.'+cast(@count+200 as varchar(50))
-- 	insert into print_device 
-- 	values(@devno,@ip,9100,1,GETDATE())
-- 	set @count = @count +1
-- end
-- 
--****************************************分割线*****************************************************
--创建任务表
use printer
if exists( select * from sys.sysobjects where name='print_task')
  drop table print_task
go

CREATE table print_task(
	id int identity(1,1) not null PRIMARY KEY,
	ip varchar(50),--ip地址
	port int,--端口
	destination varchar(50) ,--目的地
	departure_place varchar(50) ,--始发地
	barcode varchar(50) ,--条码
	simple_barcode varchar(50) ,--简码
	to_pos varchar(50) ,--格口
  number int DEFAULT 0,--件数
	is_printed int default 0,--是否完成打印任务：0，未完成；1已完成
	creatime datetime  DEFAULT GETDATE(),
	updatatime datetime
)
go

--插入测试语句
-- declare @test_insert_count int = 0
-- while @test_insert_count<50
-- 	begin
-- 		insert into print_task(ip,port,destination,departure_place,barcode,simple_barcode,to_pos,number)
-- 		VALUES('10.66.252.169',9100,'南京','上海','123456789','ST87','55',44)	
-- 		set @test_insert_count = @test_insert_count + 1
-- 	end
-- go
--测试
--select * from print_task ORDER BY creatime
--每个打印机添加一条数据

-- declare @count int = 1
-- DECLARE @ip varchar(50)
-- while @count<=20
-- 	begin
-- 		set @ip = '10.66.252.'+cast(@count+200 as varchar(50))
-- 		insert into print_task(ip,port,destination,departure_place,barcode,simple_barcode,to_pos,number)
-- 		VALUES(@ip,9100,'南京','上海','123456789','ST87','55',@count)	
-- 		set @count = @count +1
-- 	end
-- go
--****************************************分割线*****************************************************
--创建日志表
use printer
if exists( select * from sys.sysobjects where name='print_log')
  drop table print_log
go

CREATE table print_log(
	id int identity(1,1) not null PRIMARY KEY,
	procedure_name varchar(50),
	print_task_id int,--任务表id
	result_state varchar(50),--执行完成状态：0，失败；1成功！
	creatime datetime  DEFAULT GETDATE(),
	updatatime datetime
)
go

--插入信息
-- insert into print_log(procedure_name,print_task_id,result_state)
-- values('test_procedure',8,'1')
--****************************************分割线*****************************************************
--生成报文的存储过程
if exists(select * from sys.sysobjects where name='sp_crerte_print_msg')
begin
drop procedure sp_crerte_print_msg
end
go

create PROCEDURE sp_crerte_print_msg
@input_ip varchar(50),
@input_port int,
@input_time varchar(50),
@output_id int output,
@output_msg varchar(8000) output,
@output_log_msg varchar(5000) output
--@output_ip varchar(50) output,
--@output_port int output
as
begin
DECLARE 
	@id int,
	@destination varchar(50) ,--目的地
	@departure_place varchar(50) ,--始发地
	@barcode varchar(50) ,--条码
	@simple_barcode varchar(50) ,--简码
	@to_pos varchar(50) ,--格口
  @number int --件数
	
DECLARE @temp_msg varchar(8000)
	
	--set rowcount 1
	--查找变量的方式即使没有数据也不为空
	select
		top 1
		@id=id,
		@destination=destination,
		@departure_place=departure_place,
		@barcode=barcode,
		@simple_barcode=simple_barcode,
		@to_pos=to_pos,
		@number=number 
	from print_task
	where is_printed=0 and ip=@input_ip and port=@input_port
	order by creatime
	--拼装条码
	DECLARE @temp_barcode varchar(100)
	set @temp_barcode = '>:'+SUBSTRING(@barcode, 1, 1)
	set @temp_barcode = @temp_barcode+'>5'+SUBSTRING(@barcode, 2, 14)
	set @temp_barcode = @temp_barcode+'>6'+SUBSTRING(@barcode, 16, 2)
	set @temp_barcode = @temp_barcode+'>5'+SUBSTRING(@barcode, 18, 4)
	set @temp_barcode = @temp_barcode+'>6'+SUBSTRING(@barcode, 22, 1)
	--update print_task set is_print='1' where id=@id
	--多个字符串拼接，只要有一个变量为空，结果就为空
	set @temp_msg= '^XA
									^CI28^CW1,E:SIMSUN.FNT								
									^MD5^PW440^LL735
									^FO193,230^GB121,0,1^FS
									^FO193,400^GB121,0,1^FS
									^FO313,2^GB0,734,1^FS
									^FO193,2^GB0,734,1^FS
									^FO313,2^GB127,160,127^FS
									^FT340,20^A1R,100,124^FR^FD包^FS
									^FT404,160^A1R,30,30^FH\^FD目的地^FS
									^FT405,161^A1R,30,30^FH\^FD目的地^FS							
									^FT340,160^A1R,40,40^FH\^FD'+@destination+'^FS
									^FT341,161^A1R,40,40^FH\^FD'+@destination+'^FS
									^FT342,162^A1R,40,40^FH\^FD'+@destination+'^FS
									
									^FT270,22^A1R,30,30^FH\^FD始发地^FS									
									^FT271,23^A1R,30,30^FH\^FD始发地^FS							
									^FT217,22^A1R,40,40^FH\^FD'+@departure_place+'^FS
									^FT218,23^A1R,40,40^FH\^FD'+@departure_place+'^FS
									^FT219,24^A1R,40,40^FH\^FD'+@departure_place+'^FS
									
									^FT275,240^A1R,30,30^FH\^FD件数:^FS
									^FT276,241^A1R,30,30^FH\^FD件数:^FS
									^FT275,330^A1R,40,40^FH\^FD'+cast(@number as varchar(9))+'^FS
									^FT276,331^A1R,40,40^FH\^FD'+cast(@number as varchar(9))+'^FS
									^FT277,332^A1R,40,40^FH\^FD'+cast(@number as varchar(9))+'^FS
									
									^FT220,240^A1R,30,30^FH\^FD格口:^FS
									^FT221,241^A1R,30,30^FH\^FD格口:^FS
									^FT220,330^A1R,40,40^FH\^FD'+@to_pos+'^FS
									^FT221,331^A1R,40,40^FH\^FD'+@to_pos+'^FS
									^FT222,332^A1R,40,40^FH\^FD'+@to_pos+'^FS
									
									^FT220,410^A1R,60,60^FH\^FD'+@simple_barcode+'^FS
									^FT220,411^A1R,60,60^FH\^FD'+@simple_barcode+'^FS
									^FT220,412^A1R,60,60^FH\^FD'+@simple_barcode+'^FS
								
									^BY2,3,130^FT40,100^BCR,,Y,N^FD'+@barcode+'^FS
									^PQ1,0,1,Y
									^XZ'
-- 	set @temp_count=@@rowcount
	set @output_id=@id
	set @output_msg=ISNULL(@temp_msg, '')
	set @output_log_msg=	@input_ip+','
											+cast(@input_port as varchar(50))+','
											+cast(@id as varchar(50))+','
											+@destination+','
											+@departure_place+','
											+@barcode+','
											+@simple_barcode+','
											+@to_pos+','
											+cast(@number as varchar(50))
	--set @output_ip=ISNULL(@ip, '空')
	--set @output_port=ISNULL(@port, 0)
	--更新打印机状态
-- 	declare @printer_dev_no varchar(50)
-- 	declare @o_msg varchar(50)
-- 	declare @o_msg_str varchar(50)
-- 	set rowcount 1
-- 	select @printer_dev_no=device_number from print_device where ip=@input_ip
-- 	exec DEPPON_WCS.dbo.PROC_UPDATE_PRINTER_STATE @printer_dev_no,@input_ip,@input_port,@input_time,@o_msg OUTPUT,@o_msg_str output

end
go

--测试存储过程
-- DECLARE @test_id int,@test_msg varchar(8000),@test_ip varchar(50) = '127.0.0.1',@test_port int = 8888
-- exec sp_crerte_print_msg @test_ip,@test_port,@test_id output,@test_msg output
-- print concat('返回id：',cast(@test_id as varchar(5)))
-- print concat('返回信息：',@test_msg)
-- go


--****************************************分割线*****************************************************
--创建更新打印完成状态的存储过程
if exists(select * from sys.sysobjects where name='sp_update_is_print_to_ok')
begin
drop procedure sp_update_is_print_to_ok
end
go

CREATE PROCEDURE sp_update_is_print_to_ok
@input_id int 
as
begin
	set ROWCOUNT 1
	update print_task set is_printed=1,updatatime=GETDATE() where id=@input_id
end
go
--测试
-- exec sp_update_is_print_to_ok 2

--****************************************分割线*****************************************************
--添加打印任务的存储过程
if exists(select * from sys.sysobjects where name='sp_add_print_task')
begin
drop procedure sp_add_print_task
end
go

CREATE PROCEDURE sp_add_print_task
@input_ip varchar(50),
@input_port int,
@input_destination varchar(50) ,--目的地--DEST_NAME
@input_departure_place varchar(50) ,--始发地
@input_barcode varchar(50) ,--条码
@input_simple_barcode varchar(50) ,--简码--DESTINATION
@input_to_pos varchar(50) ,--格口
@input_number int, --件数
@output_msg varchar(50) output --1：成功；0：失败

--@output_ip varchar(50) output,
--@output_port int output
as
begin
	begin try
		insert into print_task(ip,port,destination,departure_place,barcode,simple_barcode,to_pos,number)
			VALUES(
						@input_ip,
						@input_port,
						@input_destination,
						@input_departure_place,
						@input_barcode,
						@input_simple_barcode,
						@input_to_pos,
						@input_number
						)
						set @output_msg='1'
		DECLARE @temp_id int 
		set rowcount 1
		select @temp_id=id from print_task order by id desc
		insert into print_log(procedure_name,print_task_id,result_state)
		values('sp_add_print_task',@temp_id,'1')
		
	end try
	
	begin catch
		set @output_msg='0'
		insert into print_log(procedure_name,print_task_id,result_state)
		values('sp_add_print_task',-1,'0')
	end catch
end
go